package zy.dao.stock.useable.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.stock.useable.UseableDAO;
import zy.entity.stock.useable.T_Stock_Useable;
import zy.entity.sys.user.T_Sys_User;
import zy.util.DateUtil;
import zy.util.UseableStockUtil;

@Repository
public class UseableDAOImpl extends BaseDaoImpl implements UseableDAO{

	@Override
	public List<T_Stock_Useable> list(T_Sys_User user) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT t.ua_code,t.ua_name,t.ua_remark,t.ua_type,ua.ua_id,ua.ua_join,companyid ");
		sql.append(" FROM common_useable t");
		sql.append(" LEFT JOIN t_stock_useable ua ON t.ua_code = ua.ua_code AND ua.companyid = :companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(t.ua_ver,:ua_ver) > 0");
		sql.append(" AND t.ua_state = :ua_state");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("ua_ver", user.getCo_ver()).addValue("ua_state", 1).addValue("companyid", user.getCompanyid()),
				new BeanPropertyRowMapper<>(T_Stock_Useable.class));	
	}

	@Override
	public void save(List<T_Stock_Useable> datas) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_stock_useable");
		sql.append(" (ua_code,ua_join,companyid)");
		sql.append(" VALUES(:ua_code,:ua_join,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(datas.toArray()));
	}

	@Override
	public void update(List<T_Stock_Useable> datas) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_useable");
		sql.append(" SET ua_join = :ua_join");
		sql.append(" WHERE ua_id = :ua_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(datas.toArray()));
	}

	@Override
	public Map<String, Object> loadUseableStock(String pd_code, String dp_code, Integer companyid) {
		final Map<String, Object> stockMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ua_code"); 
		sql.append(" FROM t_stock_useable");
		sql.append(" WHERE ua_join = 1");
		sql.append(" AND companyid = :companyid");
		List<String> useableModule = namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("companyid", companyid), String.class);
		if(useableModule.size() == 0){
			return stockMap;
		}
		String date = DateUtil.getDateAddMonths(-3);//三月内的可用库存
		
		MapSqlParameterSource parameterSource = new MapSqlParameterSource()
				.addValue("dp_code", dp_code).addValue("pd_code", pd_code)
				.addValue("date", date).addValue("companyid", companyid);
		
		if(useableModule.contains(UseableStockUtil.BATCH_ORDER_0)){//批发订单
			sql.setLength(0);
			sql.append("SELECT odl_sub_code AS sub_code,-SUM(odl_amount-odl_realamount) AS amount");
			sql.append(" FROM t_batch_order t");
			sql.append(" JOIN t_batch_orderlist odl ON od_number = odl_number AND t.companyid = odl.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND (od_ar_state = 0 OR (od_ar_state = 1 AND od_state IN(0,4)))");
			sql.append(" AND odl_amount > odl_realamount");
			sql.append(" AND od_type = 0");
			sql.append(" AND od_depot_code = :dp_code");
			sql.append(" AND odl_pd_code = :pd_code");
			sql.append(" AND od_sysdate >= :date");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY odl_sub_code");
			queryToMap(sql.toString(), parameterSource, stockMap);
		}
		if(useableModule.contains(UseableStockUtil.BATCH_SELL_0)){//批发出库单
			sql.setLength(0);
			sql.append(" SELECT sel_sub_code AS sub_code,-SUM(sel_amount) AS amount"); 
			sql.append(" FROM t_batch_sell t");
			sql.append(" JOIN t_batch_selllist sel ON se_number = sel_number AND t.companyid = sel.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND se_ar_state <> 1");
			sql.append(" AND se_type = 0");
			sql.append(" AND se_depot_code = :dp_code");
			sql.append(" AND sel_pd_code = :pd_code");
			sql.append(" AND se_sysdate >= :date");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY sel_sub_code");
			queryToMap(sql.toString(), parameterSource, stockMap);
		}
		if(useableModule.contains(UseableStockUtil.BUY_ENTER_0)){//采购入库单
			sql.setLength(0);
			sql.append(" SELECT etl_sub_code AS sub_code,SUM(etl_amount) AS amount");
			sql.append(" FROM t_buy_enter t");
			sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND et_ar_state <> 1");
			sql.append(" AND et_type = 0");
			sql.append(" AND et_depot_code = :dp_code");
			sql.append(" AND etl_pd_code = :pd_code");
			sql.append(" AND et_sysdate >= :date");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY etl_sub_code");
			queryToMap(sql.toString(), parameterSource, stockMap);
		}
		if(useableModule.contains(UseableStockUtil.STOCK_ALLOCATE)){//仓库调拔单
			sql.setLength(0);
			sql.append(" SELECT sub_code,SUM(amount) AS amount");
			sql.append(" FROM (");
			sql.append(" SELECT acl_sub_code AS sub_code,SUM(acl_amount) AS amount");
			sql.append(" FROM t_stock_allocate t");
			sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND ac_ar_state <> 1");
			sql.append(" AND ac_indp_code = :dp_code");
			sql.append(" AND acl_pd_code = :pd_code");
			sql.append(" AND ac_sysdate > :date");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY acl_sub_code");
			sql.append(" UNION ALL");
			sql.append(" SELECT acl_sub_code AS sub_code,-SUM(acl_amount) AS amount");
			sql.append(" FROM t_stock_allocate t");
			sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND ac_ar_state <> 1");
			sql.append(" AND ac_outdp_code = :dp_code");
			sql.append(" AND acl_pd_code = :pd_code");
			sql.append(" AND ac_sysdate > :date");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY acl_sub_code");
			sql.append(" )t");
			sql.append(" GROUP BY sub_code");
			sql.append(" HAVING amount <> 0");
			queryToMap(sql.toString(), parameterSource, stockMap);
		}
		if(useableModule.contains(UseableStockUtil.SHOP_WANT_0)){//前台补货单
			sql.setLength(0);
			sql.append(" SELECT wtl_sub_code AS sub_code,SUM(IF(wtl_applyamount=0,wtl_sendamount,wtl_applyamount)) AS amount");
			sql.append(" FROM t_shop_want t");
			sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND wt_ar_state IN(0,1,2)");//0未审核 1 未发货 2已退回
			sql.append(" AND wt_type = 0");
			sql.append(" AND wt_outdp_code = :dp_code");
			sql.append(" AND wtl_pd_code = :pd_code");
			sql.append(" AND wt_sysdate > :date");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY wtl_sub_code");
			queryToMap(sql.toString(), parameterSource, stockMap);
		}
		return stockMap;
	}
	
	private void queryToMap(String sql,MapSqlParameterSource parameterSource,final Map<String, Object> stockMap){
		namedParameterJdbcTemplate.query(sql.toString(), parameterSource, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String sub_code = rs.getString("sub_code");
				Integer amount = rs.getInt("amount");
				if(stockMap.containsKey(sub_code)){
					stockMap.put(sub_code,(Integer)stockMap.get(sub_code)+amount);
				}else{
					stockMap.put(sub_code,amount);
				}
				return null;
			};
		});
	}

}
